package com.jp.fm.datacollector;

import static org.junit.Assert.fail;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.TimeUnit;

import org.apache.commons.lang3.StringUtils;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.openqa.selenium.By;
import org.openqa.selenium.ElementNotVisibleException;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;

import com.jp.fm.HomeFinanceRunner;
import com.jp.fm.dao.AccountDao;
import com.jp.fm.data.Account;
import com.jp.fm.data.Category;
import com.jp.fm.data.Transaction;
import com.jp.fm.data.UserAccount;

public class SelNTSCUSavings extends SelParent {

	private WebDriver driver;
	private StringBuffer verificationErrors = new StringBuffer();

	@Before
	public final void setUp() throws Exception {
		this.driver = new FirefoxDriver();
		this.driver.manage().timeouts()
				.implicitlyWait(AllTests.WAIT_TIME_SECONDS, TimeUnit.SECONDS);
	}

	@Test
	public final void testSelNTSCUSavings() throws Exception {

		final SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");

		Account account = getAccount();

		// Lookup username, password and url
		UserAccount uAcct = UserAccount.findByName(account.getName());
		String url = uAcct.getAttribute(UserAccount.URL);
		String username = uAcct.getUsername();
		String password = uAcct.getPassword();
		String name = uAcct.getName();

		this.driver.get(url);
		this.driver.switchTo().frame("Center");
		this.driver.findElement(By.name("LOGNID")).clear();
		this.driver.findElement(By.name("LOGNID")).sendKeys(username);
		this.driver.findElement(By.name("OLB_CMD-SMS-001~OLB_CMD-SMS-000"))
				.click();
		// this.driver.switchTo().frame("Center");
		this.driver.findElement(By.name("PARAM_PASSWORD")).sendKeys(password);
		this.driver.findElement(By.cssSelector("input.Normal_Button")).submit();

		// Click account detail
		this.driver.switchTo().frame("Center");
		this.driver.findElement(
				By.xpath("//a[starts-with(@title, 'Link to account detail *"
						+ account.getAccountNumber() + "=A')]")).click();

		// Click more button
		try {
			this.driver.findElement(By.name("OLB_CMD-SHB-134")).click();
		} catch (ElementNotVisibleException e) {
			// If the more button is not visible then proceed.
			e.printStackTrace();
		}

		// Get balance
		String balStr = this.driver
				.findElement(
						By.xpath("//div[@class='downtr-250']//table//tbody//tr[3]//td[2]"))
				.getText();
		balStr = StringUtils.remove(balStr, '$');
		balStr = StringUtils.remove(balStr, ' ');
		Number number = NumberFormat.getInstance().parse(balStr);
		Float balance = number.floatValue();

		WebElement table = this.driver.findElement(By.id("067"));
		List<WebElement> transRows = table.findElements(By.tagName("tr"));

		int counter = 0;
		List<Transaction> trans = new ArrayList<Transaction>();
		Transaction tran = null;

		for (Iterator<WebElement> iterator = transRows.iterator(); iterator
				.hasNext();) {
			WebElement webElement = iterator.next();

			if (counter == 0) {
				counter++;
				continue;
			}

			List<WebElement> transCols = webElement.findElements(By
					.tagName("td"));

			String dateEl = transCols.get(0).getText();
			Date date = sdf.parse(dateEl);
			Calendar cal = new GregorianCalendar();
			cal.setTime(date);
			cal.set(Calendar.HOUR_OF_DAY, 1);
			cal.set(Calendar.MINUTE, 0);
			cal.set(Calendar.SECOND, 0);
			cal.set(Calendar.MILLISECOND, 0);
			date = cal.getTime();

			String desc = transCols.get(1).getText();

			String amtEl = transCols.get(3).getText();
			amtEl = StringUtils.remove(amtEl, '$');
			amtEl = StringUtils.remove(amtEl, ' ');
			number = NumberFormat.getInstance().parse(amtEl);
			Float amount = number.floatValue();

			Category category = getCategoryDao().findById(
					getCategoryService().getCategoryByKeyword(desc));

			tran = new Transaction(date, null, name, amount, category, "na",
					desc, false, null);
			trans.add(tran);
			counter++;
		}

		// Log out
		// this.driver.findElement(By.xpath("//a[starts-with(@title, 'Exit')]")).click();

		// Write transactions to DB
		Integer insertCount = 0;
		for (Iterator<Transaction> iterator = trans.iterator(); iterator
				.hasNext();) {
			Transaction record = iterator.next();

			Connection c = null;
			PreparedStatement p = null;

			try {

				c = HomeFinanceRunner.getConnection();

				p = c.prepareStatement("INSERT INTO PUBLIC.TRANSACTIONS "
						+ "( DATE, ACCOUNT, AMOUNT, CATEGORY_ID, DESCRIPTION, "
						+ "EXTERNAL_ID, CONFIRMED) " + "VALUES (?,?,?,?,?,?,0)");

				p.setDate(HomeFinanceRunner.DB_COLUMN_FIRST, new java.sql.Date(
						record.getDate().getTime()));
				p.setString(HomeFinanceRunner.DB_COLUMN_SECOND,
						record.getAccount());
				p.setFloat(HomeFinanceRunner.DB_COLUMN_THIRD,
						record.getAmount());
				p.setInt(HomeFinanceRunner.DB_COLUMN_FOURTH, record
						.getCategory().getId());
				p.setString(HomeFinanceRunner.DB_COLUMN_FIFTH,
						record.getDescription());
				p.setString(HomeFinanceRunner.DB_COLUMN_SIXTH,
						record.getExternalId());

				p.executeUpdate();

				c.commit();
				insertCount++;

			} catch (SQLException e) {

				if (!e.getMessage().contains(
						new StringBuffer("integrity constraint violation"))) {
					e.printStackTrace();
				}

			} finally {

				HomeFinanceRunner.closeConnection(c, p, null);
			}

		}

		// Update account balance
		account.setBalance((balance));
		getAccountDao().writeObject(account);
		System.out.println("Account updated");

		System.out.println(insertCount + " records inserted");

		HomeFinanceRunner.getCache(AccountDao.CACHE_NAME).removeAll();
		System.out.println("Account< " + account.getName() + "> cache cleared");
	}

	@After
	public final void tearDown() throws Exception {
		this.driver.quit();
		String verificationErrorString = this.verificationErrors.toString();
		if (!"".equals(verificationErrorString)) {
			fail(verificationErrorString);
		}
	}
}
